﻿CREATE PROCEDURE [dbo].[proc_Order_Change_Getlist_In]
	(
		@InCompanyId varchar(500),
		@flag int,
		@ChangeType int,
		@Stext nvarchar(50),
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
Declare @cSql varchar(2000)

Set @cSql = 'a.CompanyId In('+@InCompanyId+') And shqrq>=@sRq And shqrq<=@eRq'
if @flag<>100
	Set @cSql = @cSql + ' And a.flag='+Convert(varchar(10),@flag)

if @ChangeType<>100
	Set @cSql = @cSql + ' And a.ChangeType='+Convert(varchar(10),@ChangeType)

if @Stext<>''
	Set @cSql = @cSql + ' And (a.CusName like ''%'+@Stext+'%'' Or a.shqr like ''%'+@Stext+'%'')'

Set @cSql='Declare @sRq Datetime,@eRq Datetime
Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')
;WITH list As(Select ROW_NUMBER() OVER (ORDER BY shqrq Desc,a.Id DESC)AS Row
		,a.Id
		,a.OId
		,a.CusId
		,a.CusName
		,shqr
		,shqrxm
		,shqrq
		,bgnr
		,oldje
		,newje
		,a.flag
,IsNull(b.ShiShouPrice,0) As ShiShouPrice
,a.CompanyId
,a.ChangeType
,b.NOE_Flag
--,Isnull((select Pizhu from Customer_PiZhu where CustomerId=a.CusId),'''') as Pizhu --批注内容字段 2015.08.20 
--		,Isnull((select CusState from Customer_PiZhu where CustomerId=a.CusId),0) as CusState --是否有批注字段 
		From [Order_Change] a,[Order] b
		Where '+@cSql + '
		And a.OId=b.OId
	)

	Select *,(Select Count(0) From list) As RecordCount From list Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex)+' Order By Row'
--Select @cSql
Exec(@cSql)
End
